import pandas as pd
import numpy as np
import os
import pymysql
from scipy.stats import mode
import collections
import sys

sys.path.append("..")


def selector_to_dict(path, pkl_choose_num=100, total_choose_num=10):
    pkllists = os.listdir(path)
    y_list = []
    for pkllist in pkllists:
        pkl_path = path + os.path.sep + pkllist
        data = pd.read_pickle(pkl_path)
        for item in data:
            if item['y_name'] not in y_list:
                y_list.append(item['y_name'])

    res_dict = {}
    for y_name in y_list:
        res_dict[y_name] = [[], []]

    for pkllist in pkllists:
        pkl_path = path + os.path.sep + pkllist
        data = pd.read_pickle(pkl_path)
        for item in data:
            y_name = item['y_name']
            features = list(item['features'])[:pkl_choose_num]  # 设置变量
            res_dict[y_name][0] = res_dict[y_name][0] + features
        print(pkllist + '已经完成统计')

    selected_res_dict = dict()
    for y_name in y_list:
        count_num = collections.Counter(res_dict[y_name][0])
        count_num = dict(count_num)
        count_num = sorted(count_num.items(), key=lambda item: item[1], reverse=True)
        selected_res_dict[y_name] = []
        for i in range(total_choose_num):
            selected_res_dict[y_name].append(count_num[i][0])
    return selected_res_dict


def dict_to_dataframe(res_dict, target_excel_path):
    res_dataframe = pd.DataFrame()
    for key, value in res_dict.items():
        y_name = key
        features = value
        for i, feature in enumerate(features):
            res_dataframe = res_dataframe.append([{'y_name': y_name, 'num': i, 'feature': feature}], ignore_index=True)
        print("key为\"" + y_name + "\"的dict处理完毕")

    res_dataframe.index = list(range(res_dataframe.shape[0]))
    res_dataframe.to_excel(target_excel_path)
    return res_dataframe


def update_to_mysql(res_dataframe):
    conn = pymysql.connect(host='192.168.3.36', port=3306, user='root', password='280303', db='features',
                           charset="utf8")

    for i in range(res_dataframe.shape[0]):
        y_name = list(res_dataframe['y_name'])[i]
        feature = list(res_dataframe['feature'])[i]

        cur = conn.cursor()
        cur.execute("select count(*) from feature where y_name=%s", y_name)
        num = cur.fetchall()[0][0]
        cur.close()

        cur2 = conn.cursor()
        cur2.execute("select count(*) from feature where y_name=%s and feature=%s", (y_name, feature))
        res = cur2.fetchall()[0][0]
        cur2.close()
        if res == 0:
            print("(" + y_name + "," + feature + ")" + "不存在")

            cur3 = conn.cursor()
            sql = "insert into feature(y_name, num, feature) values (%s, %s, %s)"
            values = []
            values.append(y_name)
            values.append(num)
            values.append(feature)
            values = tuple(values)
            cur3.execute(sql, values)
            print("已经插入" + y_name + "的特征" + feature)
            cur3.close()

        else:
            print("(" + y_name + "," + feature + ")" + "已存在")

    conn.commit()
    conn.close()


def select_from_mysql(y_name_list):
    res_dict = {}
    for y_name in y_name_list:
        res_dict[y_name] = []

    conn = pymysql.connect(host='192.168.3.36', port=3306, user='root', password='280303', db='features',
                           charset="utf8")

    for y_name in y_name_list:
        cur = conn.cursor()
        cur.execute("select feature from feature where y_name=%s", y_name)
        res = cur.fetchall()
        cur.close()

        if len(res) == 0:
            print("不存在" + y_name + "的特征")
            break

        for i in range(len(res)):
            res_dict[y_name].append(res[i][0])

    conn.commit()
    conn.close()
    return res_dict


if __name__ == '__main__':
    path = 'D:/work/HF/2021_03_08/output/selector/selector_2021_03_10_23_57_32_441218'
    target_excel_path = "../../output/features/feature_result.xlsx"
    res_dict = selector_to_dict(path, pkl_choose_num=1000, total_choose_num=1500)
    res_dataframe = dict_to_dataframe(res_dict, target_excel_path)
    update_to_mysql(res_dataframe)

# =============================================================================
#     y_name_list = ['服务业生产指数_当月同比', 'CPI_不包括食品和能源(核心CPI)_当月同比']
#     result_dict = select_from_mysql(y_name_list)
# 
# =============================================================================
